﻿CREATE PROCEDURE [dbo].[proc_CallCenter_Recordlist]
(
	@CompanyId int,
	@DepartmentId int,
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),
	@Stext nvarchar(15),
	@StartIndex int,
	@EndIndex int,
	@telFlag VARCHAR(10)
)
AS
Begin
	Declare @cSql nvarchar(4000),@cWhere nvarchar(400)

	Set @cWhere=''
	If @Stext<>''
	Begin
		If @telFlag='1'	--呼入/出电话号
			Set @cWhere =' And TEL='''+@Stext+''''
		Else If @telFlag='2'	--分机号
			Set @cWhere =' And DepartmentId>0 And ZuoxiID In(Select Top 1 ZXNum From CallCenter Where CompanyId='+Convert(varchar(10),@CompanyId)+' And FJNum='+@Stext+')'
		Else If @telFlag='3'	--坐席号
			Set @cWhere =' And ZuoxiID='+@Stext
		Else	--员工姓名
			Set @cWhere =' And EName='''+@Stext+''''
	End
	If @DepartmentId>0
		Set @cWhere = @cWhere+' And r.DepartmentId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)'

	Set @cSql='Declare @sRq Datetime,@eRq Datetime
		Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
		Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

		Declare @bmbh_T varchar(30),@CompanyName nvarchar(100)
		
		Select @CompanyName=IsNull(CompanyName,''---'') From Company Where Id='+Convert(varchar(10),@CompanyId)+'
		Select @bmbh_T=bmbh From department Where Id='+Convert(varchar(10),@DepartmentId)+'

		;WITH list As (Select Top 3000 ROW_NUMBER() OVER (ORDER BY r.Id DESC)AS Row,
			Id,
			GId,
			ZuoxiID,
			[Type],
			Tel,
			IsNull(StartDate,''1900-01-01'') AS StartDate,
			IsNull(EndDate,''1900-01-01'') AS EndDate,	
			TxtFileName,
			FileName,
			CreateDate,
			CompanyId,
			DepartmentId,
			UserName,
			IsNull(EName,''---'') As EName
			From CallCenter_Record r
			Where r.CompanyId='+Convert(varchar(10),@CompanyId)+' And EndDate>=@sRq And EndDate<=@eRq'+@cWhere+'
		)

		Select *,
			--(Case When [Type]=''CH_FREE'' Then 0 Else IsNull(DateDiff(ss,StartDate,EndDate),0) End) As Seconds,
			0 As Seconds,
			(Case When UserName Is Null Then -1 Else IsNull((Select Top 1 FJNum From CallCenter Where ZXNum=list.ZuoxiID And CompanyId='+Convert(varchar(10),@CompanyId)+'),-1) End) As FJNum,
			@CompanyName As CompanyName,
			(Case When UserName Is Null Then ''---'' Else IsNull((Select DName From Department Where Id=list.DepartmentId),''---'') End) As Department,
			(Select Count(0) From list) As Count_Sum
		From list Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+' Order By Row'

	Execute(@cSql)
End
